package com.sihai.ehr.service.impl;

import com.sihai.core.*;
import com.sihai.ehr.jdbc.KaoQinService;
import com.sihai.ehr.model.biz.*;
import com.sihai.ehr.model.sys.*;
import com.sihai.ehr.service.SalaryAttendanceService;
import com.sihai.ehr.utils.ExcelUtils;

import org.apache.commons.lang.math.NumberUtils;
import org.apache.commons.lang.xwork.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import javax.annotation.Resource;

import java.io.File;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * author: wxn
 * since: 13-1-21 Time: 上午10:07
 */
@Service("salaryAttendanceService")
public class SalaryAttendanceServiceImpl implements SalaryAttendanceService {
    @Resource
    private DataAccessor dataAccessor;


    //从考勤数据库导数据开始===========================================================================================
    //导入考勤数据
    @Override
    public String importAttendanceDataFromDB(final Long organizationId, final Integer year, final Integer month) throws Exception {
        if(organizationId == null || organizationId <= 0){
            return "organization_id_null";
        }
        if(year == null || year <=0 || month == null || month <= 0){
            return "year_month_null";
        }

        //所有员工数据
        List<EmployeeinfoWork> works = dataAccessor.findByHql(EmployeeinfoWork.class, "from EmployeeinfoWork w where w.baseId.state='1' and w.baseId.cancel='0' and w.cancel='0' and (w.organization.id=? or w.organization.topOrganization.id=?)", new Object[]{organizationId, organizationId}, null, null);
        if(works == null || works.isEmpty()){
            return "no_employees";
        }

        //本月已导入的数据
        List<AttendanceNew> oldAttendances = dataAccessor.findByHql(AttendanceNew.class, "from AttendanceNew a where a.attendYear = ? and a.attendMonth = ? and a.organizationId = ?", new Object[]{year, month, organizationId}, null, null);

        KaoQinService kaoQinService = new KaoQinService();
        kaoQinService.connection();
        List<AttendanceNew> attendances = kaoQinService.findByYearAndMonth(oldAttendances, works, year, month);
        if(attendances == null || attendances.isEmpty()){
            return "no_data_in_db";
        }

        StringBuffer sb = new StringBuffer();
        for(AttendanceNew attendance : attendances){
            try{
                attendance.setOrganizationId(organizationId);
                dataAccessor.save(attendance);
            }catch (Exception e){
                sb.append("考勤工号").append(attendance.getEmployeeinfoWork().getWorkNo()).append("导入失败\n");
                e.printStackTrace();
            }
        }
        //查找指定年月已导入记录数
        Long successCount = dataAccessor.findUnique(Long.class, new QueryCreator() {
            @Override
            public Query createQuery(Session session) {
                Query query = session.createQuery("select count(a.id) from AttendanceNew a where a.attendYear = :y and a.attendMonth = :m and a.organizationId = :oid");
                query.setParameter("y", year);
                query.setParameter("m", month);
                query.setParameter("oid", organizationId);
                return query;
            }
        });
        //查找总员工数
        Long employeeCount = findEmployeeCount(organizationId);

        AttendanceImportLog log = findAttendanceLog(organizationId, year, month, "attendance");
        if(log == null){
            log = new AttendanceImportLog();
        }
        log.setOrganizationId(organizationId);
        log.setYear(year);
        log.setMonth(month);
        log.setSuccessCount((int)((long)successCount));
        log.setTotal((int)((float)employeeCount));
        log.setType("attendance");
        log.setProgress(((float)successCount/(float)employeeCount)*100);
        dataAccessor.save(log);

        if(sb.length() == 0){
            return "success";
        }
        return sb.toString();
    }
    //从考勤数据库导数据完成===========================================================================================


    //导入考勤数据
    @Override
    public String importAttendanceData(File file, final Long organizationId, final Integer year, final Integer month) throws Exception {
        if(file == null){
            return "file_not_found";
        }
        if(organizationId == null || organizationId <= 0){
            return "organization_id_null";
        }
        List<String[]> rows = ExcelUtils.readExcelUsePoi(file, 0, 1);
        if(rows == null || rows.isEmpty()){
            return "file_is_empty";
        }

        //String fileName = file.getName(); //attendance_datas_2012_12.xls
        //int offset = "attendance_datas_".length();
        //final int year = NumberUtils.createInteger(StringUtils.substring(fileName, offset, offset + 4));
        //final int month = NumberUtils.createInteger(StringUtils.substring(fileName, offset+5, fileName.lastIndexOf(".")));
        if(year == null || year <=0 || month == null || month <= 0){
            return "year_month_null";
        }

        List<EmployeeinfoWork> works = dataAccessor.findByHql(EmployeeinfoWork.class, "from EmployeeinfoWork w where w.baseId.state='1' and w.baseId.cancel='0' and w.cancel='0'", null, null, null);
        if(works == null || works.isEmpty()){
            return "no_employees";
        }

        //批量清除指定年月的考勤数据
        /*dataAccessor.batchUpdate(new QueryCreator() {
            @Override
            public Query createQuery(Session session) {
                Query query = session.createQuery("delete from Attendance a where a.attendYear = :year and a.attendMonth = :month and a.organizationId = :organizationId");
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.setParameter("organizationId", organizationId);
                return query;
            }
        });*/
        //本月已导入的数据
        List<Attendance> oldAttendances = dataAccessor.findByHql(Attendance.class, "from Attendance a where a.attendYear = ? and a.attendMonth = ? and a.organizationId = ?", new Object[]{year, month, organizationId}, null, null);

        StringBuffer sb = new StringBuffer();
        for(String[] row : rows){
            EmployeeinfoWork work = getEmployeeinfoWork(works, row[1]);
            if(work == null){
                sb.append("考勤工号").append(row[1]).append("找不到对应的员工\n");
                continue;
            }
            try{
                Attendance attendance = findAttendanceByErpNo(oldAttendances, row[1]);
                if(attendance == null){
                    attendance = new Attendance();
                }
                attendance.setOrganizationId(organizationId);
                attendance.setAttendYear(year);
                attendance.setAttendMonth(month);
                attendance.setEmployeeinfoWork(work);

                attendance.setWorkDays(createFloat(row[5])); //上班天数
                attendance.setOutDays(createFloat(row[6])); //外出天数
                attendance.setHolidayDays(createFloat(row[7])); //假日天数
                attendance.setAbsentDays(createFloat(row[8])); //旷工天数
                attendance.setTotalOvertimeDays(createFloat(row[9])); //总计加班天数
                attendance.setAbsenceLeaveDays(createFloat(row[10])); //事假天数
                attendance.setSickLeaveDays(createFloat(row[11])); //病假天数
                attendance.setMarryLeaveDays(createFloat(row[12])); //婚假天数
                attendance.setMaternityLeaveDays(createFloat(row[13])); //产假天数
                attendance.setAdjustDays(createFloat(row[14])); //调休天数
                attendance.setYearLeaveDays(createFloat(row[15])); //年假天数
                attendance.setFurloughLeaveDays(createFloat(row[16])); //休假天数
                attendance.setOtherLeaveDays(createFloat(row[17])); //其它假天数
                attendance.setFuneralLeaveDays(createFloat(row[18])); //丧假天数
                attendance.setInjuryDays(createFloat(row[19])); //工伤天数
                attendance.setTotalLeaveDays(createFloat(row[20])); //请假总计天数
                attendance.setOverTimeOneDays(createFloat(row[21])); //加班段1时长
                attendance.setOverTimeTwoDays(createFloat(row[22])); //加班段2时长
                attendance.setOverWeekendDays(createFloat(row[23])); //周末加班时长
                attendance.setAdjustLeave(createFloat(row[24])); //调休时长
                attendance.setFuneralLeave(createFloat(row[25])); //丧假时长
                attendance.setInjuryLeave(createFloat(row[26])); //工伤时长
                attendance.setDelayTimes(createInteger(row[27])); //迟到总次数
                attendance.setDelayLeave(createFloat(row[28])); //迟到总时长
                attendance.setEarlyTimes(createInteger(row[29])); //早退总次数
                attendance.setEarlyLeave(createFloat(row[30])); //早退总时长
                attendance.setUnCardTimes(createInteger(row[31])); //未打卡次数
                attendance.setReCardTimes(createInteger(row[32])); //考勤补签次数
                attendance.setSaturdayDays(createFloat(row[33])); //周六加班
                attendance.setSundayDays(createFloat(row[34])); //周日加班
                attendance.setSmallNight(createInteger(row[35])); //小夜
                attendance.setBigNight(createInteger(row[36])); //大夜
                attendance.setWashHours(createFloat(row[37])); //应勤小时数
                attendance.setWashDays(createFloat(row[38])); //应勤天数
                dataAccessor.save(attendance);
            }catch (Exception e){
                sb.append("考勤工号").append(row[1]).append("导入失败\n");
                e.printStackTrace();
            }
        }
        //查找指定年月已导入记录数
        Long successCount = dataAccessor.findUnique(Long.class, new QueryCreator() {
            @Override
            public Query createQuery(Session session) {
                Query query = session.createQuery("select count(a.id) from Attendance a where a.attendYear = :y and a.attendMonth = :m and a.organizationId = :oid");
                query.setParameter("y", year);
                query.setParameter("m", month);
                query.setParameter("oid", organizationId);
                return query;
            }
        });
        //查找总员工数
        Long employeeCount = findEmployeeCount(organizationId);

        AttendanceImportLog log = findAttendanceLog(organizationId, year, month, "attendance");
        if(log == null){
            log = new AttendanceImportLog();
        }
        log.setOrganizationId(organizationId);
        log.setYear(year);
        log.setMonth(month);
        log.setSuccessCount((int)((long)successCount));
        log.setTotal((int)((float)employeeCount));
        log.setType("attendance");
        log.setProgress(((float)successCount/(float)employeeCount)*100);
        dataAccessor.save(log);

        if(sb.length() == 0){
            return "success";
        }
        return sb.toString();
    }
    private Float createFloat(String str){
        if(StringUtils.isBlank(str)){
            return null;
        }
        try {
            return Float.parseFloat(str);
        } catch (NumberFormatException e) {
            return null;
        }
    }
    private Integer createInteger(String str){
        if(StringUtils.isBlank(str)){
            return null;
        }
        try {
            return Integer.parseInt(str);
        } catch (NumberFormatException e) {
            return null;
        }
    }
    //通过考勤编号(也叫erp编号)查找员工
    private EmployeeinfoWork getEmployeeinfoWork(List<EmployeeinfoWork> works, String erpNo){
        if(works == null || works.isEmpty()){
            return null;
        }
        for(EmployeeinfoWork work : works){
            if(StringUtils.equals(erpNo, work.getBaseId().getSalaryNo())){
                return work;
            }
        }
        return null;
    }
    private Attendance findAttendanceByErpNo(List<Attendance> attendances, String erpNo){
        if(attendances == null || attendances.isEmpty() || StringUtils.isBlank(erpNo)){
            return null;
        }
        for(Attendance a : attendances){
            if(StringUtils.equals(a.getEmployeeinfoWork().getBaseId().getSalaryNo(), erpNo)){
                return a;
            }
        }
        return null;
    }

    @Override
    public LimitedList<Attendance> find(int year, int month, Long organizationId, Long dutyId, Limit limit, Sorter sorter) throws Exception{
        if(organizationId == null){
            return null;
        }
        String hql = "from Attendance a where a.attendYear=? and a.attendMonth=? and a.employeeinfoWork.organization.treePath like ? and a.employeeinfoWork.cancel='0' and a.employeeinfoWork.baseId.cancel='0' and a.employeeinfoWork.baseId.state='1'";
        String treePath = "%,"+organizationId.longValue()+",%";
        Object[] args = {year, month, treePath};
        if(dutyId != null && dutyId > 0){
            hql += " and a.employeeinfoWork.duty.id=?";
            args = new Object[]{year, month, treePath, dutyId};
        }
        return dataAccessor.findByHql(Attendance.class, hql, args, limit, sorter); //注意like的参数不加单引号（"%,1,%"）

    }
    
    @Override
    public LimitedList<Attendance> findAttendancesByWorkId(Long workId,
    		int startYear, int startMonth, int endYear, int endMonth,
    		Limit limit, Sorter sorter) throws Exception {
    	if(workId == null){
            return null;
        }
        String hql = "from Attendance a where a.employeeinfoWork.id=" + workId + " and a.employeeinfoWork.cancel='0' ";
        if(startYear > -1){
        	hql += " and (a.attendYear>" + startYear + " or a.attendYear=" + startYear + " and a.attendMonth>" + startMonth + ")";
        	hql += " and (a.attendYear<" + endYear + " or a.attendYear=" + endYear + " and a.attendMonth<" + endMonth+ ")";
        }
        return dataAccessor.findByHql(Attendance.class, hql, null, limit, sorter); //注意like的参数不加单引号（"%,1,%"）
    }

    //导入erp数据
    @Override
    public String importERPData(File file, final Long organizationId, final Integer year, final Integer month) {
        if(file == null){
            return "file_not_found";
        }
        List<String[]> rows = ExcelUtils.readExcelUsePoi(file, 0, 1);
        if(rows == null || rows.isEmpty()){
            return "file_is_empty";
        }

        //String fileName = file.getName(); //erp_datas_2012_12.xls
        //int offset = "erp_datas_".length();
        //final int year = NumberUtils.createInteger(StringUtils.substring(fileName, offset, offset + 4));
        //final int month = NumberUtils.createInteger(StringUtils.substring(fileName, offset+5, fileName.lastIndexOf(".")));
        if(year == null || year <=0 || month == null || month <= 0){
            return "year_month_null";
        }

        List<EmployeeinfoWork> works = dataAccessor.findByHql(EmployeeinfoWork.class, "from EmployeeinfoWork w where w.baseId.state='1' and w.baseId.cancel='0' and w.cancel='0'", null, null, null);
        if(works == null || works.isEmpty()){
            return "no_employees";
        }

        //批量清除指定年月的考勤数据
        /*dataAccessor.batchUpdate(new QueryCreator() {
            @Override
            public Query createQuery(Session session) {
                Query query = session.createQuery("delete from AttendanceErp a where a.attendYear = :year and a.attendMonth = :month and a.organizationId = :organizationId");
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.setParameter("organizationId", organizationId);
                return query;
            }
        });*/
        //本月已导入的数据
        List<AttendanceErp> oldAttendanceErps = dataAccessor.findByHql(AttendanceErp.class, "from AttendanceErp a where a.attendYear = ? and a.attendMonth = ? and a.organizationId = ?", new Object[]{year, month, organizationId}, null, null);


        StringBuffer sb = new StringBuffer();
        for(String[] row : rows){
            EmployeeinfoWork work = getEmployeeinfoWork(works, row[1]);
            if(work == null){
                sb.append("ERP工号").append(row[1]).append("找不到对应的员工\n");
                continue;
            }
            try{
                AttendanceErp erp = findAttendanceErpByErpNo(oldAttendanceErps, row[1]);
                if(erp == null){
                    erp = new AttendanceErp();
                }
                erp.setOrganizationId(organizationId);
                erp.setAttendYear(year);
                erp.setAttendMonth(month);
                erp.setEmployeeinfoWork(work);

                erp.setTotal(createFloat(row[3])); //数据
                erp.setAmount(createFloat(row[4])); //金额
                dataAccessor.save(erp);
            }catch (Exception e){
                sb.append("ERP工号").append(row[1]).append("导入失败\n");
                e.printStackTrace();
            }
        }

        //查找指定年月已导入记录数
        Long successCount = dataAccessor.findUnique(Long.class, new QueryCreator() {
            @Override
            public Query createQuery(Session session) {
                Query query = session.createQuery("select count(a.id) from AttendanceErp a where a.attendYear = :y and a.attendMonth = :m and a.organizationId = :oid");
                query.setParameter("y", year);
                query.setParameter("m", month);
                query.setParameter("oid", organizationId);
                return query;
            }
        });
        //查找总员工数
        Long employeeCount = findEmployeeCount(organizationId);

        AttendanceImportLog log = findAttendanceLog(organizationId, year, month, "erp");
        if(log == null){
            log = new AttendanceImportLog();
        }
        log.setOrganizationId(organizationId);
        log.setYear(year);
        log.setMonth(month);
        log.setSuccessCount((int)((long)successCount));
        log.setTotal((int)((float)employeeCount));
        log.setType("erp");
        log.setProgress(((float)successCount/(float)employeeCount)*100);
        dataAccessor.save(log);

        if(sb.length() == 0){
            return "success";
        }
        return sb.toString();
    }
    private AttendanceErp findAttendanceErpByErpNo(List<AttendanceErp> erps, String erpNo){
        if(erps == null || erps.isEmpty() || StringUtils.isBlank(erpNo)){
            return null;
        }
        for(AttendanceErp e : erps){
            if(StringUtils.equals(e.getEmployeeinfoWork().getBaseId().getSalaryNo(), erpNo)){
                return e;
            }
        }
        return null;
    }


    @Override
    public List<AttendanceImportLog> findAttendanceLog(Long organizationId, Integer year, Integer month) throws Exception{
        Assert.notNull(organizationId, "organization is required");
        Assert.notNull(year, "year is required");
        Assert.notNull(month, "month is required");
        return dataAccessor.findByHql(AttendanceImportLog.class, "from AttendanceImportLog log where log.organizationId = ? and log.year=? and log.month=?", new Object[]{organizationId, year, month}, null, null);
    }

    private AttendanceImportLog findAttendanceLog(Long organizationId, Integer year, Integer month, String type){
        try{
            List<AttendanceImportLog> logs = findAttendanceLog(organizationId, year, month);
            if(logs == null || logs.isEmpty()){
                return null;
            }
            for(AttendanceImportLog log : logs){
                if(StringUtils.equals(log.getType(), type)){
                    return log;
                }
            }
        }catch (Exception e){

        }
        return null;
    }

    @Override
    public LimitedList findAttendances(String type, Long organizationId, Integer year, Integer month, Limit limit, Sorter sorter) {
        Assert.notNull(organizationId, "organization is required");
        Assert.notNull(year, "year is required");
        Assert.notNull(month, "month is required");
        Object[] args = {organizationId, year, month};
        if(StringUtils.equals(type, "attendance")){
            //return dataAccessor.findByHql(Attendance.class, "from Attendance a where a.organizationId=? and a.attendYear=? and a.attendMonth=?", args, limit, sorter);
            return dataAccessor.findByHql(AttendanceNew.class, "from AttendanceNew a where a.organizationId=? and a.attendYear=? and a.attendMonth=?", args, limit, sorter);
        }else if(StringUtils.equals(type, "erp")){
            return dataAccessor.findByHql(AttendanceErp.class, "from AttendanceErp erp where erp.organizationId=? and erp.attendYear=? and erp.attendMonth=?", args, limit, sorter);
        }
        return null;
    }

    //清除指定年月的考勤数据
    @Override
    public void delete(final Long organizationId, final Integer year, final Integer month, String type)throws Exception{
        Assert.notNull(organizationId, "organization is required");
        Assert.notNull(year, "year is required");
        Assert.notNull(month, "month is required");

        if(StringUtils.equals("attendance", type)){
            //批量清除指定年月的考勤数据
            dataAccessor.batchUpdate(new QueryCreator() {
                @Override
                public Query createQuery(Session session) {
                    //Query query = session.createQuery("delete from Attendance a where a.attendYear = :year and a.attendMonth = :month and a.organizationId = :organizationId");
                    Query query = session.createQuery("delete from AttendanceNew a where a.attendYear = :year and a.attendMonth = :month and a.organizationId = :organizationId");
                    query.setParameter("year", year);
                    query.setParameter("month", month);
                    query.setParameter("organizationId", organizationId);
                    return query;
                }
            });
        }else if(StringUtils.equals("erp", type)){
            //批量清除指定年月的erp数据
            dataAccessor.batchUpdate(new QueryCreator() {
                @Override
                public Query createQuery(Session session) {
                    Query query = session.createQuery("delete from AttendanceErp a where a.attendYear = :year and a.attendMonth = :month and a.organizationId = :organizationId");
                    query.setParameter("year", year);
                    query.setParameter("month", month);
                    query.setParameter("organizationId", organizationId);
                    return query;
                }
            });
        }else{
            throw new IllegalArgumentException("type must required");
        }

        AttendanceImportLog log = findAttendanceLog(organizationId, year, month, type);
        log.setSuccessCount(0);
        log.setProgress(0);
        dataAccessor.save(log);
    }

    //查找分公司已完成考核的总人数(应该提到repository层，暂省略)
    @Override
    public Long findExamCount(final Long organizationId, final Integer year, final Integer month) {
        Long count = dataAccessor.findUnique(Long.class, new QueryCreator() {
            @Override
            public Query createQuery(Session session) {
                Query query = session.createQuery("select count(e.id) from ExamStatistics e where e.examPeriod.examYear=:y and e.examPeriod.examMonthly=:m and e.employeeinfoWork.organization.topOrganization.id=:topid");
                query.setParameter("y", year);
                query.setParameter("m", month);
                query.setParameter("topid", organizationId);
                return query;
            }
        });
        //查找周期数
        List<String> examPeriods = dataAccessor.findByHql(String.class, "select distinct(e.periodType) from ExamBaseConfig e where e.duty.organization.id=? or e.duty.organization.topOrganization.id=?", new Object[]{organizationId, organizationId}, null, null);
        if(examPeriods == null || examPeriods.isEmpty()){
            return 0L;
        }
        return (long)Math.ceil(count/examPeriods.size());
    }

    //查找分公司总人数(应该提到repository层，暂省略)
    @Override
    public Long findEmployeeCount(final Long organizationId){
        return dataAccessor.findUnique(Long.class, new QueryCreator() {
            @Override
            public Query createQuery(Session session) {
                Query query = session.createQuery("select count(w.id) from EmployeeinfoWork w where w.cancel = '0' and w.baseId.state='1' and w.baseId.cancel='0' and (w.organization.id=:oid or w.organization.topOrganization.id=:topid)");
                query.setParameter("topid", organizationId);
                query.setParameter("oid", organizationId);
                return query;
            }
        });
    }
}
